[SQL 2] 테이블 생성, MySQL 테이블 데이터 형식

1. 생성 (DB, Table)

데이터 베이스 생성하기

show databases;

CREATE Database {DB이름};
USE {DB이름};

데이터베이스 설계

Table을 생성하는 방법

1. 테이블 설계 ( 예시 )

• 회원 테이블( member)

• 제품 테이블(product table)

1. 테이블 설계 코드

• Member 테이블

CREATE TABLE member(  
member_num INT AUTO_INCREMENT,  
member_id VARCHAR(30) NOT NULL,  
member_name VARCHAR(20) NOT NULL,  
member_addr VARCHAR(100) NOT NULL,  
member_phone VARCHAR(11) NOT NULL,  
member_home_number VARCHAR(11) NOT NULL,
  PRIMARY KEY (member_num)
)ENGINE=INNODB CHARSET=UTF8;

• 테이블마다 고유의 값을 나타내는 PRIMARRY KEY 는 필수적입니다.

• 이후에 따라오는 ENGINE과 CHARSET의 경우 필수는 아니나, CHARSET의 경우 UTF-8등 인코딩 방식을 지정하지 않으면 DB안에 있는 데이터가 깨질 수 있습니다.

• product 테이블

CREATE TABLE product(
product_serial	VARCHAR(20) NOT NULL,
product_name	VARCHAR(100) NOT NULL,
product_price	INT NOT NULL,
product_manufacture	DATE NOT NULL,
product_company	VARCHAR(30) NOT NULL,
product_remaining	INT NOT NULL,
 PRIMARY KEY (product_serial)
)ENGINE=INNODB CHARSET=UTF8;

2. MySQL 데이터 형식

2.1 타입(Data Type)

• 테이블을 정의할 때는 필드별로 저장할 수 있는 타입까지 명시해야 합니다.

• MySQL에서 제공하는 기본 타입은 다음과 같습니다.

1. 숫자 타입

2. 문자열 타입

3. 날짜와 시간 타입

2.2 숫자 타입(Numeric Types)

• MySQL은 SQL 표준에서 지원하는 모든 숫자 타입을 제공합니다.

1. 정수 타입(Integer Type)

2. 고정 소수점 타입(Fixed-Point Type)

3. 부동 소수점 타입(Floating-Point Type)

4. 비트값 타입(Bit-Value Type)

• MySQL은 SQL 표준 정수 타입인 INTEGER(또는 INT)와 SMALLINT를 제공합니다.

• 또한, 표준 정수 타입의 범위를 더욱 확장한 TINYINT, MEDIUMINT, BIGINT 까지 제공하고 있습니다.

• 각 정수 타입에 따라 요구되는 저장 공간과 표현할 수 있는 최댓값과 최솟값이 다릅니다.

• 또한, 정수 타입은 음수까지 표현할 수 있는 SIGNED 타입과 양수만을 표현할 수 있는 UNSIGNED 타입으로도 구분됩니다.

• MySQL에서 고정 소수점 타입인 DECIMAL은 실수의 값을 정확하게 표현하기 위해 사용됩니다.

• DECIMAL에서 사용하는 고정 소수점 방식은 실수를 표현할 때 소수부의 자릿수를 고정하여 표현합니다.

• 즉, 소수부의 자릿수를 미리 정해 놓고, 고정된 자릿수로만 소수 부분을 표현하는 방식입니다.

DECIMAL(M, D)

• M은 소수 부분을 포함한 실수의 총 자릿수를 나타내며, 최댓값은 65입니다.

• D는 소수 부분의 자릿수를 나타내며, D가 0이면 소수부분을 가지지 않는 것입니다.

• MySQL에서 부동 소수점 타입인 FLOAT와 DOUBLE은 실수의 값을 대략적으로 표현하기 위해 사용됩니다.

• FLOAT는 4바이트를 사용하며, DOUBLE은 8바이트를 사용합니다.

FLOAT(P)

• FLOAT는 정밀도에 필요한 최소한의 비트 수를 명시할 수 있습니다.

• P가 0부터 24까지의 값을 가질때는 FLOAT 값으로 취급되며, 25부터 53까지의 값을 가질때는 DOUBLE 값으로 취급됩니다.

• MySQL에서 비트값 타입인 BIT는 비트의 값을 저장합니다.

• 즉, 0과 1로 구성되는 바이너리 값을 저장할 수 있습니다.

BIT(M)

• M의 범위는 1부터 64까지 설정할 수 있으며, 명시한 M 비트의 값을 저장할 수 있습니다.

• 만약 명시한 M 비트보다 짧은 길이의 비트 값을 입력하면, 입력한 값 앞에 0을 추가하여 자동으로 길이를 맞춥니다.

2.3 문자열 타입

• CHAR와 VARCHAR 둘다 문자열 데이터를 저장할 수 있는 타입입니다.

• 하지만 저장 방식과 추출 방식 그리고 최대 길이를 다루는 방식에서 차이점을 가집니다.

• CHAR는 문자열을 길이가 한번 설정되면 그대로 고정되는 고정 길이의 문자열을 다룹니다.

• VARCHAR는 문자열을 길이가 고정되지 않는 가변 길이의 문자열로 다룹니다.

• NCHAR는 길이만큼 고정된 크기의 유니코드 문자열을 저장합니다. 이때, 한글(2바이트), 영문(1바이트)과 같은 다국어 문자를 저장할 때 유용합니다

CHAR(MAX)  //CHAR(2 CHAR) 형식으로도 가능
VARCHAR(MAX)
NCHAR(길이)

• MAX은 저장할 수 있는 문자열의 최대 길이를 나타냅니다. (MAX Byte)

• CHAR는 설정한 크기보다 작은 길이의 문자열이 입력되면, 나머지 공간을 공백으로 채워 길이를 M과 같게 만듭니다.

3. 대용량 데이터 다루기

• TEXT 타입은 대용량의 문자열 데이터를 저장할 때 사용합니다.

• MySQL에서는 그 크기에 따라 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT와 같이 분류됩니다.

• 각 타입별로 저장할 수 있는 문자열의 길이와 저장 공간이 다릅니다.

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

• TINYTEXT : 최대 255바이트 까지의 작은 문자열을 저장.

• TEXT : 최대 65,535바이트 까지의 문자열을 저장.

• MEDIUMTEXT : 최대 16,777,215바이트 까지의 중간 크기의 문자열을 저장.

• LONGTEXT : 최대 4GB 까지의 큰 문자열을 저장.

• BLOB 타입은 TEXT 타입과 비슷하게 대용량의 바이너리 데이터를 저장할 때 사용합니다.

• MySQL에서는 그 크기에 따라 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB와 같이 분류됩니다.

• 각 타입별로 저장할 수 있는 바이너리 데이터의 크기와 저장 공간이 다릅니다.

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

• TINYBLOB : 최대 255바이트 까지의 작은 바이너리 데이터를 저장.

• BLOB : 최대 65,535바이트 까지의 바이너리 데이터를 저장.

• MEDIUMBLOB : 최대 16,777,215바이트 까지의 중간 크기의 바이너리 데이터를 저장.

• LONGBLOB : 최대 4GB 까지의 큰 바이너리 데이터를 저장.

• CLOB(Character Large OBject), NCLOB(National Character Large OBject) 타입은 대용량의 문자열 데이터를 저장할 때 사용합니다.

• CLOB은 아스키 문자 집합을 사용하여 문자열을 저장하고, NCLOB은 유니코드 문자 집합을 사용하여 문자열을 저장합니다.

• CLOB과 NCLOB은 VARCHAR 타입과 유사하게 작동하지만, 문자열의 길이 제한이 없다는 점에서 차이가 있습니다.

• CLOB은 TEXT 타입과, NCLOB은 MEDIUMTEXT 타입과 유사합니다.

CLOB
NCLOB

• CLOB : 최대 4GB 까지의 큰 문자열을 저장.

• NCLOB : 최대 4GB 까지의 큰 유니코드 문자열을 저장.

4. 날짜와 시간 타입

• DATE
• DATE는 날짜를 저장할 수 있는 타입입니다.
• 기본 형식은 'YYYY-MM-DD' 입니다.

• DATETIME
• DATETIME은 날짜와 함께 시간까지 저장할 수 있는 타입입니다.
• 기본 형식은 'YYYY-MM-DD HH:MM:SS' 입니다.

• TIMESTAMP
• TIMESTAMP는 날짜와 시간을 나타내는 타임스탬프를 저장할 수 있는 타입입니다.
• TIMESTAMP 타입의 필드는 사용자가 별다른 입력을 주지 않으면, 데이터가 마지막으로 입력되거나 변경된 시간이 저장됩니다.
• 따라서, 데이터의 최종 변경 시각을 저장하고 확인하는 데 유용하게 사용됩니다.

• TIME
• TIME은 시간을 저장할 수 있는 타입입니다.
• 기본 형식은 'HH:MM:SS' 입니다.

• YEAR
• YEAR는 연도를 저장할 수 있는 타입입니다.
• YEAR(2)는 2자리의 연도를 저장할 수 있으며, YEAR(4)는 4자리의 연도를 저장할 수 있습니다.

5. DB 엔진

• MyISAM은 MySQL에서 가장 오래 된 엔진 중 하나입니다.

• MyISAM은 데이터의 읽기 속도가 빠르고, 인덱스를 사용한 검색 속도가 빠릅니다.

• 하지만, MyISAM은 트랜잭션 처리를 지원하지 않습니다.

• 또한, 테이블 단위로 락(LOCK)이 걸리기 때문에 여러 사용자가 동시에 작업을 할 경우 락 경합으로 인한 딜레이가 발생할 수 있습니다.

• MyISAM은 전체 테이블을 잠그기 때문에 대용량 데이터 처리 시 성능에 문제가 생길 수 있습니다.

• InnoDB는 MySQL에서 가장 많이 사용되는 엔진 중 하나입니다.

• InnoDB는 트랜잭션 처리를 지원하고, 롤백(ROLLBACK) 기능을 제공하여 데이터 무결성을 보장합니다.

• 또한, InnoDB는 행 단위 락(ROW LOCK)을 사용하기 때문에 여러 사용자가 동시에 작업해도 락 경합이 발생하지 않습니다.

• InnoDB는 대용량 데이터 처리에도 높은 성능을 보여줍니다.

• 하지만, InnoDB는 MyISAM에 비해 약간의 오버헤드가 발생하기 때문에 속도가 느릴 수 있습니다.